from flask import Blueprint, render_template, jsonify, request, send_file
from specific_personnel_exchange_anakysis import SpecificPersonnelExchangeAnalyzer
import pandas as pd
import numpy as np
from io import BytesIO
import traceback
from openpyxl.utils import get_column_letter
from openpyxl.styles import Alignment
from pathlib import Path
# 创建Blueprint
specific_personnel_exchange_bp = Blueprint('specific_personnel_exchange', __name__)
analyzer = SpecificPersonnelExchangeAnalyzer()
# 初始化数据跟路径
analyzer.get_data_dir()
def get_resource_path(relative_path):
    return analyzer.get_base_path(relative_path)
@specific_personnel_exchange_bp.route('/specific_personnel_exchange')
def specific_personnel_exchange():
    return render_template('pages/specific_personnel_exchange.html', active_page='specific_personnel_exchange')

@specific_personnel_exchange_bp.route('/check-data', methods=['GET'])
def check_data():
    """检查是否存在资产关系数据，同时返回亲属关系数据"""
    try:
        return jsonify(analyzer.check_data('银行流水数据库.xlsx', 'source'))

    except Exception as e:
        print(f"[DEBUG] 发生错误: {str(e)}")
        
        traceback.print_exc()
        return jsonify({
            'exists': False,
            'message': f'检查数据时出错: {str(e)}',
            'folders': []
        })

@specific_personnel_exchange_bp.route('/get-analysis', methods=['GET'])
def get_analysis():
    # 获取选中的文件夹、亲属和资产类型
    subdirectories = request.args.getlist('folders')
    selected_relatives = request.args.getlist('relatives')
    selected_relatives1 = request.args.getlist('relatives1')
    is_excel = request.args.get('is_excel')
    print(f"[DEBUG] 选中的文件夹: {subdirectories}")
    print(f"[DEBUG] 选中的亲属: {selected_relatives}")
    print(f"[DEBUG] 选中的交易对方名称: {selected_relatives1}")
    data = []
    check_file_type = request.args.get('check_file_type')
    filepath = request.args.get('filepath')
    yhFile = None
    dir_path = None
    if check_file_type == "2":
        if not filepath:
            return jsonify({
                'success': False,
                'message': '自定义文件路径为空'
            })
        filepath_parts = filepath.split('\\')
        if len(filepath_parts) < 2:
            return jsonify({
                'success': False,
                'message': '自定义文件路径格式错误'
            })
        yhFile = filepath_parts[1]
        dir_path = Path(filepath_parts[0]).absolute()
        dir_path.mkdir(parents=True, exist_ok=True)
    combined_df = None  # 初始化combined_df
    try:
        # 初始化资源路径
        analyzer.get_source_dir('source')
        # 获取资源路径下的所有子目录名称
        if not subdirectories:
            subdirectories = analyzer.get_subdirectories('source')
        # 处理文件名称
        filename = '银行流水数据库.xlsx'
        if len(subdirectories) > 5:
            return jsonify({
                'success': False,
                'message': '选中的被审查人数不能超过5个'
            })
        # 获取数据
        data = []
        for item in subdirectories:
            file_path = analyzer.source_dir / item
            # 处理银行流水数据
            if check_file_type == "2":
                bank_file_path = dir_path / yhFile
                print(f"尝试访问的文件路径: {bank_file_path}")
                if not bank_file_path.exists():
                    return jsonify({
                        'success': False,
                        'message': f'自定义文件不存在'
                    })
                df = analyzer.read_excel_files(dir_path, yhFile)
            else:
                bank_file_path = file_path / filename
                if not bank_file_path.exists():
                    return jsonify({
                        'success': False,
                        'message': f'{item}的银行数据流水.xlsx文件不存在'
                    })
                df = analyzer.read_excel_files(file_path, filename)
            # df = analyzer.read_excel_files(file_path, filename)
            # 根据名称在selected_relatives队列中的值与借贷标志进行分组，统计交易对方名称在selected_relatives1队列中的数据交易金额的总和
            if selected_relatives and selected_relatives1:
                # 筛选出名称在selected_relatives中且交易对方名称在selected_relatives1中的数据
                filtered_df = df[df['名称'].isin(selected_relatives) & df['交易对方名称'].isin(selected_relatives1)]
                if not filtered_df.empty:
                    # 按姓名和借贷标志分组，计算交易金额总和
                    grouped_df = filtered_df.groupby(['交易对方名称', '借贷标志']).agg({'交易金额': 'sum'}).reset_index()
                    grouped_df.rename(columns={'交易金额': '借贷标志对应总金额'}, inplace=True)

                    # 计算总金额，借贷标志为出的总金额减去借贷标志为进的总金额
                    pivot_df = grouped_df.pivot(index='交易对方名称', columns='借贷标志', values='借贷标志对应总金额').fillna(0)
                    pivot_df['总金额'] = pivot_df.get('出', 0) - pivot_df.get('进', 0)
                    pivot_df = pivot_df.reset_index()

                    # 将分组数据与原始数据合并，添加用于计算的相应银行流水数据
                    combined_df = pd.merge(grouped_df, pivot_df[['交易对方名称', '总金额']], on='交易对方名称')
                    # 为每条数据添加对应的原始银行流水数据
                    combined_df['关联银行流水数据'] = combined_df.apply(
                        lambda row: filtered_df[
                            (filtered_df['交易对方名称'] == row['交易对方名称']) & (filtered_df['借贷标志'] == row['借贷标志'])
                        ].to_dict('records'),
                        axis=1
                    )

        
        # 检查combined_df是否存在
        if combined_df is None or combined_df.empty:
            return jsonify({
                'success': False,
                'error': '没有找到匹配的数据'
            })

        # 获取所有列名
        columns = combined_df.columns.tolist()
        # columns = ['名称', '交易时间', '借贷标志', '交易对方名称', '单笔金额', '借贷标志对应总金额', '总金额']

        # 获取选择的字段
        selected_fields = request.args.getlist('fields')
        if selected_fields:
            # 确保所有请求的字段都存在
            valid_fields = [field for field in selected_fields if field in columns]
            if valid_fields:
                combined_df = combined_df[valid_fields]
            else:
                return jsonify({'error': '没有找到有效的字段'})
        # is_excel存在并且等于1，将字符串转换为整数后进行判断
        if is_excel and int(is_excel) == 1:
            # 创建文件名
            filename = "特定关系人员往来分析.xlsx"
            output = BytesIO()
            # 从数据中删除关联银行流水数据列
            if '关联银行流水数据' in combined_df.columns:
                combined_df = combined_df.drop(columns=['关联银行流水数据'])
                        
            with pd.ExcelWriter(output, engine='openpyxl') as writer:
                combined_df.to_excel(writer, index=False, sheet_name='Sheet1')
            output.seek(0)
            try:
                return send_file(output,
                    mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
                    as_attachment=True,
                    download_name=filename
                )
            except Exception as e:
                print(f"导出Excel文件时出错: {str(e)}")
                return jsonify({
                    'success': False,
                    'error': f'导出Excel文件时出错: {str(e)}'
                })
        elif is_excel == "2":
            # 提取所有关联银行流水数据并导出
            all_bank_flow_data = []
            for _, row in combined_df.iterrows():
                if isinstance(row.get('关联银行流水数据'), list):
                    all_bank_flow_data.extend(row['关联银行流水数据'])
            if all_bank_flow_data:
                df_bank_flow = pd.DataFrame(all_bank_flow_data)
                filename = "所有关联银行流水数据.xlsx"
                output = BytesIO()
                with pd.ExcelWriter(output, engine='openpyxl') as writer:
                    df_bank_flow.to_excel(writer, index=False, sheet_name='Sheet1')
                output.seek(0)
                try:
                    return send_file(output,
                                    mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
                                    as_attachment=True,
                                    download_name=filename
                                    )
                except Exception as e:
                    print(f"导出所有关联银行流水数据Excel文件时出错: {str(e)}")
                    return jsonify({
                        'success': False,
                        'error': f'导出所有关联银行流水数据Excel文件时出错: {str(e)}'
                    })
            else:
                return jsonify({
                    'success': False,
                    'error': '没有找到关联银行流水数据'
                })
            
        else:
            # 转换数据为字典列表
            for col in combined_df.select_dtypes(include=['datetime64']).columns:
                combined_df[col] = combined_df[col].dt.strftime('%Y-%m-%d %H:%M:%S')

            data = combined_df.to_dict('records')

            # 处理数据中的特殊值（NaN等）
            def process_value(val):
                if pd.isna(val).any() if isinstance(val, (np.ndarray, pd.Series)) else pd.isna(val):
                    return None
                elif isinstance(val, (pd.Timestamp, pd.DatetimeTZDtype)):
                    return val.isoformat()
                elif isinstance(val, (np.int64, np.float64)):
                    return float(val) if isinstance(val, np.float64) else int(val)
                return val

            for record in data:
                for key, value in record.items():
                    if key == '关联银行流水数据' and isinstance(value, list):
                        # 处理关联银行流水数据队列中的每个元素
                        for item in value:
                            for sub_key, sub_value in item.items():
                                item[sub_key] = process_value(sub_value)
                    else:
                        record[key] = process_value(value)
            return jsonify({
                'success': True,
                'data': data,
                'columns': columns,
                'total_records': len(data)
            })

    except Exception as e:
        print(f"获取数据出错: {str(e)}")  # 打印错误信息
        return jsonify({
            'success': False,
            'error': f'生成分析结果时出错: {str(e)}'
        })
